package grsu.epam.airline.DAOImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import grsu.epam.airline.DAO.IAirportDAO;
import grsu.epam.airline.DAO.IAirportInRouteDAO;
import grsu.epam.airline.model.Airport;
import grsu.epam.airline.model.AirportInRoute;

public class AirportInRouteDAOImpl implements IAirportInRouteDAO {
	private static final String GET_AIRPORTS_BY_FILGHT_QUERY = "select airportinroute_id,flight_id, airport_id,airport_queue,departure_date,arrival_date from airportinroute where flight_id=? order by airport_queue";
	private static final String ADD_AIRPORTINROUTE = "insert into AIRPORTINROUTE (flight_id,airport_id,airport_queue) values (?,?,?)";
	private static final String FIND_LAST_QUEUE_AIRPORT = "select max(airport_queue) from  airportinroute where flight_id=?";
	private static final String DELETE_AIRPORTINROUTE = "delete from airportinroute where airportinroute_id=?";

	private Connection conn;

	public AirportInRouteDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public void deleteAirportInRoute(int airportInRouteId) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(DELETE_AIRPORTINROUTE);
			stmt.setInt(1, airportInRouteId);
			stmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public List<AirportInRoute> getAirportsByFlightId(int flightId)
			throws SQLException {
		List<AirportInRoute> airportsInRoute = new ArrayList<AirportInRoute>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET_AIRPORTS_BY_FILGHT_QUERY);
			stmt.setInt(1, flightId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				IAirportDAO airportDao = new AirportDAOImpl(conn);
				Airport airport = airportDao.getAirportById(rs
						.getInt("airport_id"));
				airportsInRoute.add(new AirportInRoute(rs
						.getInt("airportinroute_id"), rs.getInt("flight_id"),
						rs.getInt("airport_queue"), airport));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return airportsInRoute;
	}

	@Override
	public int getQueue(int flightId) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int airportQueue = 0;
		try {
			stmt = conn.prepareStatement(FIND_LAST_QUEUE_AIRPORT);
			stmt.setInt(1, flightId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				airportQueue = rs.getInt("max(airport_queue)");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return airportQueue;
	}

	@Override
	public void addAirportInRoute(int flightId, int airportId)
			throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(ADD_AIRPORTINROUTE);
			stmt.setInt(1, flightId);
			stmt.setInt(2, airportId);
			stmt.setInt(3, (getQueue(flightId) + 1));
			// SimpleDateFormat sdf = new
			// SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			// Calendar cal=Calendar.getInstance();
			// stmt.setTimestamp(4, sdf.format(cal.getTime()));
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}

			}
		}
	}

	@Override
	public Connection getConnection() {
		return this.conn;
	}
}
